SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;
	l_total_extracted_2 NUMBER;
	l_total_imported_2 NUMBER;				
	l_func_id			VARCHAR2(100):='CAPPOREC_APPROVAL_GROUP';
	l_code VARCHAR2(100):= UPPER('&1');

BEGIN
	
	select COUNT(1)
	into l_total_extracted
   	from   PO_CONTROL_GROUPS_ALL PCG,
	       HR_ORGANIZATION_INFORMATION HOI
   	where  HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
   	and    HOI.ORG_INFORMATION3 =   ( select set_of_books_id
      							      from gl_sets_of_books
      								  where upper(short_name) = upper(l_code))
   	and    HOI.ORGANIZATION_ID = PCG.ORG_ID;
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_PO_CONTROL_GROUPS_ALL;

	select COUNT(1) 
	into l_total_extracted_2
   	from   PO_CONTROL_RULES
   	where  CONTROL_GROUP_ID in 
					 (select PCG.CONTROL_GROUP_ID
					  from   PO_CONTROL_GROUPS_ALL PCG,
			      	  HR_ORGANIZATION_INFORMATION HOI
   					  where  HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
   					  and    HOI.ORG_INFORMATION3 = ( select set_of_books_id
      							      				  from gl_sets_of_books
      								  				  where upper(short_name) = upper(l_code))
   					  and    HOI.ORGANIZATION_ID = PCG.ORG_ID
					  );

	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_PO_CONTROL_RULES;	

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted_2)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported_2));

	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	

	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
